﻿/****** Script for SelectTopNRows command from SSMS  ******/

  
  CREATE proc [dbo].[sp_clearRecords]
  as
  /*
     delete table log records ,keep a month records 
  */ 
  begin 
   
     declare @beginDate as datetime
     declare @endDate as datetime
     
     set @endDate = convert(varchar,GETDATE(),23)
     set @beginDate = DATEADD(month,-1,@enddate)

     
     declare @cmdsql as varchar(1000)
     set @cmdsql = 'select * into TempTB from log where 1=0 '
     print(@cmdsql)
     
     --insert temptb 
     set @cmdsql = 'select * into TempTB from log where createdt between '''+ convert(varchar,@beginDate,23)+ ''' and ''' + convert(varchar,@endDate,23) + ''''
     print(@cmdsql)
     
     --truncate table log 
     set @cmdsql = 'truncate table log'
     print(@cmdsql)
     
     --insert back table log 
     set @cmdsql = 'select * into log from TempTB'
     print (@cmdsql)
     
     --drop table temptb
     set @cmdsql = 'drop table TempTB'
     print (@cmdsql)
               
  end
  
  
   
  
  
  --exec sp_clearRecords 
  
  --select * from commonnotice 
  
  --insert into test select 1
